package com.sl.au.service.impl;

import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.repository.Query;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.UnexpectedRollbackException;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.sl.au.entity.Account;
import com.sl.au.entity.EXDELoginInfo;
import com.sl.au.entity.EventRegister;
import com.sl.au.entity.Option;
import com.sl.au.entity.Part;
import com.sl.au.entity.Role;
import com.sl.au.entity.TimeOutReceiveRegister;
import com.sl.au.entity.User;
import com.sl.au.repository.AccountRepository;
import com.sl.au.repository.EXDELoginInfoRepository;
import com.sl.au.repository.OptionRepository;
import com.sl.au.repository.TimeoutTaskRepository;
import com.sl.au.repository.UserRepository;
import com.sl.au.service.AccountService;
import com.sl.au.service.ModuleService;
import com.sl.au.service.TimeOutTaskService;

@Service("timeOutTaskService")
@Transactional
public class TimeOutTaskServiceImpl extends BaseServiceImpl implements TimeOutTaskService {
	
	@Autowired
	private TimeoutTaskRepository timeoutTaskRepository;
	@Autowired
	private OptionRepository optionRepository;
	
	@Resource
	private JdbcTemplate jdbcTemplate;

	@Override
	public TimeoutTaskRepository getRepository() {
		return this.timeoutTaskRepository;
	}

	@Override
	public List<EventRegister> getTimeOutTaskForReceive(int start, int length, String taskStateId, String type, String key, String orderName, String dir) {
		Page<EventRegister> pages = null;
		Pageable pageable = new PageRequest(start / length, length);
		if(StringUtils.hasText(key)) {
			if(StringUtils.hasText(type)) {
				pages = timeoutTaskRepository.getReceiveByKeyAndType(taskStateId, type, key, pageable);
			}else {
				pages = timeoutTaskRepository.getReceiveByKey(taskStateId, key, pageable);
			}
		}else {
			if(StringUtils.hasText(type)) {
				pages = timeoutTaskRepository.getReceiveAndType(taskStateId, type, pageable);
			}else {
				pages = timeoutTaskRepository.getReceive(taskStateId, pageable);
			}
		}
		return pages.getContent();
	}

	@Override
	public int getTimeOutTaskForReceiveCount(String taskStateId, String type, String key) {
		int count = 0;
		if(StringUtils.hasText(key)) {
			if(StringUtils.hasText(type)) {
				count = timeoutTaskRepository.getReceiveByKeyAndTypeCount(taskStateId, type, key);
			}else {
				count = timeoutTaskRepository.getReceiveByKeyCount(taskStateId, key);
			}
		}else {
			if(StringUtils.hasText(type)) {
				count = timeoutTaskRepository.getReceiveAndTypeCount(taskStateId,type);
			}else {
				count = timeoutTaskRepository.getReceiveCount(taskStateId);
			}
		}
		return count;
	}
	
	@Override
	public int getTimeOutReceiveCount(String type) {
		int count = 0;
		List<Option> options= optionRepository.findByGroupAndTitle("内部事件流转状态", "已分配");
		String taskStateId = "";
		
		if(options.size()>0){
			taskStateId=options.get(0).getId();
		}
		
		if(StringUtils.hasText(type)) {
			count = timeoutTaskRepository.getReceiveAndTypeCount(taskStateId,type);
		}else {
			count = timeoutTaskRepository.getReceiveCount(taskStateId);
		}
		return count;
	}

	@Override
	public List<TimeOutReceiveRegister> getTimeOutTaskForDeal(int start, int length, String taskStateId, String type, String key, String orderName, String dir) {
		Page<TimeOutReceiveRegister> pages = null;
		Pageable pageable = new PageRequest(start / length, length, Sort.Direction.DESC, "createTime");
		if(StringUtils.hasText(key)) {
			if(StringUtils.hasText(type)) {
				pages = timeoutTaskRepository.getDealByKeyAndType(taskStateId, type,key, pageable);
			}else {
				pages = timeoutTaskRepository.getDealByKey(taskStateId, key, pageable);
			}
		}else {
			if(StringUtils.hasText(type)) {
				pages = timeoutTaskRepository.getDealAndType(taskStateId, type, pageable);
			}else {
				pages = timeoutTaskRepository.getDeal(taskStateId,pageable);
			}
		}
		return pages.getContent();
	}
	
	@Override
	public int getTimeOutTaskForDealCount(String taskStateId, String type, String key) {
		int count = 0;
		if(StringUtils.hasText(key)) {
			if(StringUtils.hasText(type)) {
				count = timeoutTaskRepository.getDealByKeyAndTypeCount(taskStateId, type, key);
			}else {
				count = timeoutTaskRepository.getDealByKeyCount(taskStateId, key);
			}
		}else {
			if(StringUtils.hasText(type)) {
				count = timeoutTaskRepository.getDealAndTypeCount(taskStateId, type);
			}else {
				count = timeoutTaskRepository.getDealCount(taskStateId);
			}
		}
		return count;
	}
	
	@Override
	public int getTimeOutDealCount(String type) {
		int count = 0;
		List<Option> options= optionRepository.findByGroupAndTitle("内部事件流转状态", "处理中");
		String taskStateId = "";
		if(options.size()>0){
			taskStateId=options.get(0).getId();
		}
		if(StringUtils.hasText(type)) {
			count = timeoutTaskRepository.getDealAndTypeCount(taskStateId,type);
		}else {
			count = timeoutTaskRepository.getDealCount(taskStateId);
		}
		return count;
	}

	@Override
	public List<Map<String, Object>> getTimeOutTaskForReceiveByRole(int start, int length, String roleId, String taskStateId, String type, String key, String orderName, String dir) {

		// select distinct b from ViewForTimeOutTask a, EventRegister b where a.eventId = b.id and b.acceptLimitSpan < a.diffTime and a.eventTaskState = ?1 and a.unfinished = ?2 and (b.registerEventName like %?3% or b.registerEventType.eventName like %?3% ) order by b.createTime desc")
		
		String SQL1 = "select distinct b.*, "
				+ " et.eventName as 'eventTypeName', "
				+ " ga.gridName as 'gridName', "
				+ " d.divisionName as 'divisionName' "
				+ " from ViewForTimeOutTask a "
				+ " left join sl_wcsi_EventRegister b on b.id = a.eventId "
				+ " left join sl_dcms_eventType et on et.id = b.registerEventType "
				+ " left join sl_wcsi_gridArea ga on ga.id = b.registerEventToNetGrid "
				+ " left join sl_dcms_division d on d.id = ga.areaId "
				+ " where b.acceptLimitSpan < a.diffTime ";
		
		if(StringUtils.hasText(taskStateId)){
			SQL1+=" and a.eventTaskState = '"+taskStateId+"'";
		}

		if(StringUtils.hasText(type)){
			SQL1+=" and a.unfinished = '"+type+"'";
		}

		if(StringUtils.hasText(key)){
			SQL1+=" and (b.registerEventName like '%"+key+"%' or et.eventName like '%"+key+"%') ";	
		}
		
		
		String SQL = " select sq.* from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.registerEventType where sq.id is not null ";
		
		SQL += " order by sq." + orderName + " " + ( StringUtils.hasText(dir) ? dir : "" );
		SQL += " limit "+ (int)(Math.floor(start / length) * length) + " , " +  (int)((Math.floor(start / length) + 1) * length);
		
		List<Map<String, Object>> resultList = this.jdbcTemplate.queryForList(SQL);
		
		return resultList;
	}

	@Override
	public int getTimeOutTaskForReceiveCountByRole(String roleId, String taskStateId, String type, String key) {
		
		String SQL1 = "select distinct b.*, "
				+ " et.eventName as 'eventTypeName', "
				+ " ga.gridName as 'gridName', "
				+ " d.divisionName as 'divisionName' "
				+ " from ViewForTimeOutTask a "
				+ " left join sl_wcsi_EventRegister b on b.id = a.eventId "
				+ " left join sl_dcms_eventType et on et.id = b.registerEventType "
				+ " left join sl_wcsi_gridArea ga on ga.id = b.registerEventToNetGrid "
				+ " left join sl_dcms_division d on d.id = ga.areaId "
				+ " where b.acceptLimitSpan < a.diffTime ";
		
		if(StringUtils.hasText(taskStateId)){
			SQL1+=" and a.eventTaskState = '"+taskStateId+"'";
		}

		if(StringUtils.hasText(type)){
			SQL1+=" and a.unfinished = '"+type+"'";
		}

		if(StringUtils.hasText(key)){
			SQL1+=" and (b.registerEventName like '%"+key+"%' or et.eventName like '%"+key+"%') ";	
		}
		
		String SQL = " select count(1) from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.registerEventType where sq.id is not null ";
		
		int count = 0;
		count = this.jdbcTemplate.queryForObject(SQL, Integer.class);
		
		return count;
	}

	@Override
	public List<Map<String, Object>> getTimeOutTaskForDealByRole(int start, int length, String roleId, String taskStateId, String type, String key, String orderName, String dir) {

		// select  ViewForTimeOutTask a, EventRegister b where a.eventId = b.id and b.dealLimitSpan < a.diffTime and a.eventTaskState = ?1 and a.unfinished = ?2 and (b.registerEventName like %?3% or b.registerEventType.eventName like %?3% ) ")
		
		String SQL1 = "select b.*, "
				+ " a.eventTaskUserName, "
				+ " a.diffTime, "
				+ " et.eventName as 'eventTypeName', "
				+ " ga.gridName as 'gridName', "
				+ " d.divisionName as 'divisionName' "
				+ " from ViewForTimeOutTask a "
				+ " left join sl_wcsi_EventRegister b on b.id = a.eventId "
				+ " left join sl_dcms_eventType et on et.id = b.registerEventType "
				+ " left join sl_wcsi_gridArea ga on ga.id = b.registerEventToNetGrid "
				+ " left join sl_dcms_division d on d.id = ga.areaId "
				+ " where b.dealLimitSpan < a.diffTime ";
		
		if(StringUtils.hasText(taskStateId)){
			SQL1+=" and a.eventTaskState = '"+taskStateId+"'";
		}

		if(StringUtils.hasText(type)){
			SQL1+=" and a.unfinished = '"+type+"'";
		}

		if(StringUtils.hasText(key)){
			SQL1+=" and (b.registerEventName like '%"+key+"%' or et.eventName like '%"+key+"%') ";	
		}
		
		
		String SQL = " select sq.* from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.registerEventType where sq.id is not null ";
		
		SQL += " order by sq." + orderName + " " + ( StringUtils.hasText(dir) ? dir : "" );
		SQL += " limit "+ (int)(Math.floor(start / length) * length) + " , " +  (int)((Math.floor(start / length) + 1) * length);
		
		List<Map<String, Object>> resultList = this.jdbcTemplate.queryForList(SQL);
		
		return resultList;
	}

	@Override
	public int getTimeOutTaskForDealCountByRole(String roleId, String taskStateId, String type, String key) {
		String SQL1 = "select distinct b.*, "
				+ " et.eventName as 'eventTypeName', "
				+ " ga.gridName as 'gridName', "
				+ " d.divisionName as 'divisionName' "
				+ " from ViewForTimeOutTask a "
				+ " left join sl_wcsi_EventRegister b on b.id = a.eventId "
				+ " left join sl_dcms_eventType et on et.id = b.registerEventType "
				+ " left join sl_wcsi_gridArea ga on ga.id = b.registerEventToNetGrid "
				+ " left join sl_dcms_division d on d.id = ga.areaId "
				+ " where b.acceptLimitSpan < a.diffTime ";
		
		if(StringUtils.hasText(taskStateId)){
			SQL1+=" and a.eventTaskState = '"+taskStateId+"'";
		}

		if(StringUtils.hasText(type)){
			SQL1+=" and a.unfinished = '"+type+"'";
		}

		if(StringUtils.hasText(key)){
			SQL1+=" and (b.registerEventName like '%"+key+"%' or et.eventName like '%"+key+"%') ";	
		}
		
		String SQL = " select count(1) from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.registerEventType where sq.id is not null ";
		
		int count = 0;
		count = this.jdbcTemplate.queryForObject(SQL, Integer.class);
		
		return count;
	}

	@Override
	public int getTimeOutDealCountByRole(String roleId, String type) {
		
		List<Option> options= optionRepository.findByGroupAndTitle("内部事件流转状态", "处理中");
		String taskStateId = "";
		if(options.size()>0){
			taskStateId=options.get(0).getId();
		}
		
		//select count(*) from ViewForTimeOutTask a, EventRegister b where a.eventId = b.id and b.dealLimitSpan < a.diffTime and a.eventTaskState = ?1 and a.unfinished = ?2
		String SQL1 = "select  b.* "
				+ " from ViewForTimeOutTask a "
				+ " left join sl_wcsi_EventRegister b on b.id = a.eventId "
				+ " where b.dealLimitSpan < a.diffTime and a.eventTaskState = '"+taskStateId+"'";
		
		if(StringUtils.hasText(type)){
			SQL1+=" and a.unfinished = '"+type+"'";
		}

		String SQL = " select count(1) from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.registerEventType where sq.id is not null ";
		
		int count = 0;
		count = this.jdbcTemplate.queryForObject(SQL, Integer.class);
		
		return count;
	}

	@Override
	public int getTimeOutReceiveCountByRole(String roleId, String type) {
		List<Option> options= optionRepository.findByGroupAndTitle("内部事件流转状态", "已分配");
		String taskStateId = "";
		if(options.size()>0){
			taskStateId=options.get(0).getId();
		}
		
		//select count(*) from ViewForTimeOutTask a, EventRegister b where a.eventId = b.id and b.dealLimitSpan < a.diffTime and a.eventTaskState = ?1 and a.unfinished = ?2
		String SQL1 = "select b.* "
				+ " from ViewForTimeOutTask a "
				+ " left join sl_wcsi_EventRegister b on b.id = a.eventId "
				+ " where b.acceptLimitSpan < a.diffTime and a.eventTaskState = '"+taskStateId+"'";
		
		if(StringUtils.hasText(type)){
			SQL1+=" and a.unfinished = '"+type+"'";
		}

		String SQL = " select count(1) from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.registerEventType where sq.id is not null ";
		
		int count = 0;
		count = this.jdbcTemplate.queryForObject(SQL, Integer.class);
		
		return count;
	}
	
}
